using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using Framework.Library.Utilities;

namespace Framework.Module.Logging.Statistic
{
    public class StatisticAccessOnlineDB
    {
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch
            {
                return retVal;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetFirstAccess()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetFirstAccess", dbConn);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch
            {
                return retVal;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void Delete(string _dateAccess)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DateAccess", _dateAccess);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int Insert(StaticAccessOnlineInfo _staticAccessOnlinInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DateAccess", _staticAccessOnlinInfo.DateAccess);
            //			dbCmd.Parameters.AddWithValue("@Counter",_staticAccessOnlinInfo.Counter);
            dbCmd.Parameters.AddWithValue("@DateUpdate", _staticAccessOnlinInfo.DateUpdate);
            dbCmd.Parameters.AddWithValue("@WeekAccess", _staticAccessOnlinInfo.WeekAccess);
            dbCmd.Parameters.AddWithValue("@MonthAccess", _staticAccessOnlinInfo.MonthAccess);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static DataTable GetSumbyDay(int day, int month, int year)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticAccessOnline_GetSumByDay", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@day", day);
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally 
            {
                con.Close();
            }
        }
        public static DataTable GetSumbyDate(DateTime dates, DateTime datef)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetSumStatisticByDate", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@dates", dates);
            dbCmd.Parameters.AddWithValue("@datef", datef);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally
            {
                con.Close();
            }
        }
        //StaticAccessOnline_GetSumStatisticByDay
        public static DataTable GetSumbyDay(DateTime dates, DateTime datef)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetSumStatisticByDay", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@dates", dates);
            dbCmd.Parameters.AddWithValue("@datef", datef);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable GetSumbyWeek(DateTime dates, DateTime datef)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetSumStatisticByWeek", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@dates", dates);
            dbCmd.Parameters.AddWithValue("@datef", datef);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable GetSumbyMonth(DateTime dates, DateTime datef)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetSumStatisticByMonth", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@dates", dates);
            dbCmd.Parameters.AddWithValue("@datef", datef);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally
            {
                con.Close();
            }
        }

        public static int GetTotalbyDate(DateTime dates, DateTime datef)
        {
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetToTalByDate", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@dates", dates);
            dbCmd.Parameters.AddWithValue("@datef", datef);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                    return dr.GetInt32(0);
                else
                    return 0;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(StaticAccessOnlineInfo _staticAccessOnlinInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DateAccess", _staticAccessOnlinInfo.DateAccess);
            dbCmd.Parameters.AddWithValue("@WeekAccess", _staticAccessOnlinInfo.WeekAccess);
            dbCmd.Parameters.AddWithValue("@MonthAccess", _staticAccessOnlinInfo.MonthAccess);
            dbCmd.Parameters.AddWithValue("@DateUpdate", _staticAccessOnlinInfo.DateUpdate);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static StaticAccessOnlineInfo GetInfo(string _dateAccess)
        {
            StaticAccessOnlineInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StaticAccessOnline_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@DateAccess", _dateAccess);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new StaticAccessOnlineInfo();
                    retVal.DateAccess = Convert.ToString(dr["DateAccess"]);
                    retVal.Counter = Convert.ToInt32(dr["Counter"]);
                    retVal.DateUpdate = Convert.ToDateTime(dr["DateUpdate"]);
                    retVal.WeekAccess = Convert.ToInt32(dr["WeekAccess"]);
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable getbyday(int day, int month, int year)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticAccessOnline_GetByDay", con);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@day", day);
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
